<?php
// +----------------------------------------------------------------------
// | title: 标题注释
// +----------------------------------------------------------------------
// | Author: TZ.Leong
// +----------------------------------------------------------------------
// | Date: 2019/11/20 11:53
// +----------------------------------------------------------------------

namespace app\single\model;


use think\Collection;
use think\Db;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\exception\DbException;
use think\Model;
use think\facade\Log;

class PlayerOnlineModel extends Model
{
    protected $table = "c_player_online";

    public function playerKeep($loginDay, $regDay): array
    {
        //按注册统计留存
        $response[1] = $this->alias("a")
            ->join([$this->table => "b"], "b.player_id = a.player_id")
            ->join([config("database.game") . ".role" => "r"], "r.id = a.player_id")
            ->where(["a.day" => $loginDay, "b.day" => $regDay, "b.is_new" => 1])
            ->group("b.platform")
            ->column("COUNT(b.player_id)", "b.platform");


        //按充值统计留存
        // $data2 = $this->alias("a")
        //     ->join([$this->table => "b"], "b.player_id = a.player_id")
        //     ->where(["a.day" => $loginDay, "b.day" => $regDay, "b.is_pay" => 1])
        //     ->group("b.platform")
        //     ->column("COUNT(b.player_id)", "b.platform");

          $sql="SELECT 
  rpl.role_id
FROM role_pay_log rpl
INNER JOIN (
  -- 获取每个玩家的首次充值时间
  SELECT 
    role_id, 
    MIN(pay_time) AS min_pay_time
  FROM role_pay_log
  GROUP BY role_id
) fp ON rpl.role_id = fp.role_id AND rpl.pay_time = fp.min_pay_time
WHERE rpl.pay_date = $regDay  -- 指定查询日期
ORDER BY rpl.pay_time;";
        $paydata = Db::query($sql);
        $playerIds=[];
        foreach($paydata as $row){
            $playerIds[]= $row["role_id"];
        }
        $playerList = "(".implode(",",$playerIds).")";
        // Log::write("debug playerList ".var_export($playerList,true)."sql==<>".$sql);
        if(count($playerIds)==0){
            return $response;
        }
        $response[2] = $this->alias("a")
            ->join([$this->table => "b"], "b.player_id = a.player_id")
            ->where(["a.day" => $loginDay, "b.day" => $regDay, "b.is_pay" => 1])
            ->where("a.player_id  IN $playerList")
            ->group("b.platform")
            ->column("COUNT(b.player_id)", "b.platform");

        // $newsql = $this->getLastSql();
        // Log::write("debug sqlnew  sql==<>".$newsql."response" .json_encode($response[2]));

        // $response[2] = $this->alias("a")
        //     ->join([$this->table => "b"], "b.player_id = a.player_id")
        //     ->where(["a.day" => $loginDay, "b.day" => $regDay, "b.is_pay" => 1])
        //     ->group("b.platform")
        //     ->column("COUNT(b.player_id)", "b.platform");

    //    print_r($response);
        
        return $response;
    }

    /**
     * @param $day
     * @return array|mixed|\PDOStatement|string|Collection
     * @throws DataNotFoundException
     * @throws ModelNotFoundException
     * @throws DbException
     */
    public function statActiveRoleGold($day)
    {
        // 15天
        $dayArr = [];
        for ($i = 14; $i >= 0; $i--) {
            $dayArr[] = date("Ymd", strtotime($day) - $i * 86400);
        }
        //查询15天内累计登录3天的玩家
        $subSql = $this->whereIn("day", $dayArr)
            ->field("platform,player_id,COUNT(player_id) AS num")
            ->group("player_id")
            ->having("num >= 3")
            ->buildSql();
        // 15天内登录3天的玩家的元宝总数
        return Db::table($subSql . "AS a")
            ->leftJoin([config("database.game") . ".role_assets" => "re"], "re.role_id = a.player_id")
            ->field("platform,SUM(gold) AS gold,COUNT(role_id) AS players")
            ->group("platform")
            ->select();
    }
}